package net.hekka.canyoutalk.data.executor;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import net.hekka.canyoutalk.data.store.TempLogQuerySqlStore;
import net.hekka.canyoutalk.data.vo.TempLogQueryVo;
import net.hekka.canyoutalk.data.vo.list.TempLogQueryVoList;
import net.hekka.canyoutalk.share.database.SqlExecutor;

/**
 * 임시 로그 테이블("temp_log_table") 조회 관련 SQL문의 실행을 관리하는 클래스.
 * @author Jeffrey Ha
 */
public class TempLogQuerySqlExecutor extends SqlExecutor {

    /**
     * StatusChangeList 해당하는 TempLogQueryVoList를 얻는다.
     * @param dbConnection 데이터베이스 Connection
     * @return TempLogQueryVoList StatusChangeList 해당하는 TempLogQueryVoList
     * @throws SQLException SQL문 실행 중 오류 발생 시
     */
    public TempLogQueryVoList getTempLogQueryVoListForStatusChangeList( Connection dbConnection, int startRow, int queryCount ) throws SQLException {
        ResultSet rs = null;
        TempLogQueryVoList tempLogQueryVoList = null;
        try {
            // set Database Connection
            setDbConnection(dbConnection);
            // set Database Type
            setDatabaseType();
            // get sql statement
            String sql = TempLogQuerySqlStore.getStatusChangeListSelectSql(getDatabaseType());
            // set PrepareStatement
            setPreparedStatement(sql);
            // set parameters of Preparestatement
            this.setIntPreparedStatement(1, startRow);
            this.setIntPreparedStatement(2, queryCount);

            // execute sql
            rs = getPreparedStatement().executeQuery();
            tempLogQueryVoList = getTempLogQueryVoListFromResultSet(rs, "StatusChangeList");
        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex2) {
            }
        }
        return tempLogQueryVoList;
    }

    /**
     * BroadcastList 해당하는 TempLogQueryVoList를 얻는다.
     * @param dbConnection 데이터베이스 Connection
     * @return TempLogQueryVoList BroadcastList 해당하는 TempLogQueryVoList
     * @throws SQLException SQL문 실행 중 오류 발생 시
     */
    public TempLogQueryVoList getTempLogQueryVoListForBroadcastList( Connection dbConnection, int sequence ) throws SQLException {
        ResultSet rs = null;
        TempLogQueryVoList tempLogQueryVoList = null;
        try {
            // set Database Connection
            setDbConnection(dbConnection);
            // set Database Type
            setDatabaseType();
            // get sql statement
            String sql = TempLogQuerySqlStore.getBroadcastListSelectSql(getDatabaseType());
            // set PrepareStatement
            setPreparedStatement(sql);
            // set parameters of Preparestatement
            this.setIntPreparedStatement(1, sequence);

            // execute sql
            rs = getPreparedStatement().executeQuery();
            tempLogQueryVoList = getTempLogQueryVoListFromResultSet(rs, "BroadcastList");
        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex2) {
            }
        }
        return tempLogQueryVoList;
    }

    /**
     * FriendStateList 해당하는 TempLogQueryVoList를 얻는다.
     * @param dbConnection  데이터베이스 Connection
     * @param number 전화번호
     * @return TempLogQueryVoList FriendStateList 해당하는 TempLogQueryVoList
     * @throws SQLException SQLException SQL문 실행 중 오류 발생 시
     */
    public TempLogQueryVoList getTempLogQueryVoListForFriendStateList( Connection dbConnection, int sequence ) throws SQLException {
        ResultSet rs = null;
        TempLogQueryVoList tempLogQueryVoList = null;
        try {
            // set Database Connection
            setDbConnection(dbConnection);
            // set Database Type
            setDatabaseType();
            // get sql statement
            String sql = TempLogQuerySqlStore.getFriendStateListSelectSql(getDatabaseType());
            // set PrepareStatement
            setPreparedStatement(sql);
            // set parameters of Preparestatement
            this.setIntPreparedStatement(1, sequence);
            this.setIntPreparedStatement(2, sequence);

            // execute sql
            rs = getPreparedStatement().executeQuery();
            tempLogQueryVoList = getTempLogQueryVoListFromResultSet(rs, "FriendStateList");
        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex2) {
            }
        }
        return tempLogQueryVoList;
    }

    /**
     * MyAllFriendStateList 해당하는 TempLogQueryVoList를 얻는다.
     * @param dbConnection  데이터베이스 Connection
     * @param number 전화번호
     * @return TempLogQueryVoList MyAllFriendStateList 해당하는 TempLogQueryVoList
     * @throws SQLException SQLException SQL문 실행 중 오류 발생 시
     */
    public TempLogQueryVoList getTempLogQueryVoListForMyAllFriendStateList( Connection dbConnection, int sequence ) throws SQLException {
        ResultSet rs = null;
        TempLogQueryVoList tempLogQueryVoList = null;
        try {
            // set Database Connection
            setDbConnection(dbConnection);
            // set Database Type
            setDatabaseType();
            // get sql statement
            String sql = TempLogQuerySqlStore.getMyAllFriendStateListSelectSql(getDatabaseType());
            // set PrepareStatement
            setPreparedStatement(sql);
            // set parameters of Preparestatement
            this.setIntPreparedStatement(1, sequence);

            // execute sql
            rs = getPreparedStatement().executeQuery();
            tempLogQueryVoList = getTempLogQueryVoListFromResultSet(rs, "MyAllFriendStateList");
        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex2) {
            }
        }
        return tempLogQueryVoList;
    }

    /**
     * NormalView 해당하는 TempLogQueryVoList를 얻는다.
     * @param dbConnection 데이터베이스 Connection
     * @param startRow 조회시작순번
     * @param queryCount 1회 조회건수
     * @return TempLogQueryVoList NormalView 해당하는 TempLogQueryVoList
     * @throws SQLException SQL문 실행 중 오류 발생 시
     */
    public TempLogQueryVoList getTempLogQueryVoListForNormalView( Connection dbConnection, int startRow, int queryCount ) throws SQLException {
        ResultSet rs = null;
        TempLogQueryVoList tempLogQueryVoList = null;
        try {
            // set Database Connection
            setDbConnection(dbConnection);
            // set Database Type
            setDatabaseType();
            // get sql statement
            String sql = TempLogQuerySqlStore.getNormalViewSelectSql(getDatabaseType());
            // set PrepareStatement
            setPreparedStatement(sql);
            // set parameters of Preparestatement
            this.setIntPreparedStatement(1, startRow);
            this.setIntPreparedStatement(2, queryCount);

            // execute sql
            rs = getPreparedStatement().executeQuery();
            tempLogQueryVoList = getTempLogQueryVoListFromResultSet(rs, "NormalView");
        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex2) {
            }
        }
        return tempLogQueryVoList;
    }

    /**
     * NormalView 전체 데이터수를 구한다.
     * @param dbConnection 데이터베이스 Connection
     * @return NormalView 전체 데이터수
     * @throws SQLException SQL문 실행 중 오류 발생 시
     */
    public int getTotalCountForNormalView( Connection dbConnection ) throws SQLException {
        ResultSet rs = null;
        int totalCount = 0;
        try {
            // set Database Connection
            setDbConnection(dbConnection);
            // set Database Type
            setDatabaseType();
            // get sql statement
            String sql = TempLogQuerySqlStore.getNormalViewTotalCountSelectSql(getDatabaseType());
            // set PrepareStatement
            setPreparedStatement(sql);
            // set parameters of Preparestatement

            // execute sql
            rs = getPreparedStatement().executeQuery();
            while (rs.next()) {
                totalCount = rs.getInt("count");
            }
            
        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex2) {
            }
        }
        return totalCount;
    }

    /**
     * DetailView 해당하는 TempLogQueryVoList를 얻는다.
     * @param dbConnection 데이터베이스 Connection
     * @param startRow 조회시작순번
     * @param queryCount 1회 조회건수
     * @return TempLogQueryVoList DetailView 해당하는 TempLogQueryVoList
     * @throws SQLException SQL문 실행 중 오류 발생 시
     */
    public TempLogQueryVoList getTempLogQueryVoListForDetailView( Connection dbConnection, int startRow, int queryCount ) throws SQLException {
        ResultSet rs = null;
        TempLogQueryVoList tempLogQueryVoList = null;
        try {
            // set Database Connection
            setDbConnection(dbConnection);
            // set Database Type
            setDatabaseType();
            // get sql statement
            String sql = TempLogQuerySqlStore.getDetailViewSelectSql(getDatabaseType());
            // set PrepareStatement
            setPreparedStatement(sql);
            // set parameters of Preparestatement
            this.setIntPreparedStatement(1, startRow);
            this.setIntPreparedStatement(2, queryCount);

            // execute sql
            rs = getPreparedStatement().executeQuery();
            tempLogQueryVoList = getTempLogQueryVoListFromResultSet(rs, "DetailView");
        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex2) {
            }
        }
        return tempLogQueryVoList;
    }

    /**
     * DetailView 전체 데이터수를 구한다.
     * @param dbConnection 데이터베이스 Connection
     * @return DetailView 전체 데이터수
     * @throws SQLException SQL문 실행 중 오류 발생 시
     */
    public int getTotalCountForDetailView(Connection dbConnection) throws SQLException {
        ResultSet rs = null;
        int totalCount = 0;
        try {
            // set Database Connection
            setDbConnection(dbConnection);
            // set Database Type
            setDatabaseType();
            // get sql statement
            String sql = TempLogQuerySqlStore.getDetailViewTotalCountSelectSql(getDatabaseType());
            // set PrepareStatement
            setPreparedStatement(sql);
            // set parameters of Preparestatement

            // execute sql
            rs = getPreparedStatement().executeQuery();
            while (rs.next()) {
                totalCount = rs.getInt("count");
            }

        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex2) {
            }
        }
        return totalCount;
    }


    /**
     * StatusChange 전체 데이터수를 구한다.
     * @param dbConnection 데이터베이스 Connection
     * @return StatusChange 전체 데이터수
     * @throws SQLException SQL문 실행 중 오류 발생 시
     */
    public int getTotalCountForStatusChange(Connection dbConnection) throws SQLException {
        ResultSet rs = null;
        int totalCount = 0;
        try {
            // set Database Connection
            setDbConnection(dbConnection);
            // set Database Type
            setDatabaseType();
            // get sql statement
            String sql = TempLogQuerySqlStore.getStatusChangeTotalCountSelectSql(getDatabaseType());
            // set PrepareStatement
            setPreparedStatement(sql);
            // set parameters of Preparestatement

            // execute sql
            rs = getPreparedStatement().executeQuery();
            while (rs.next()) {
                totalCount = rs.getInt("count");
            }

        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex2) {
            }
        }
        return totalCount;
    }

    /**
     * 나의 전화번호에 해당하는 최대 Sequence값을 구한다.
     * @param dbConnection 데이터베이스 Connection
     * @param myNumber 나의 전화번호
     * @return 나의 전화번호에 해당하는 최대 Sequence값
     * @throws SQLException SQL문 실행 중 오류 발생 시
     */
    public int getMaxSequenceOfMyNumber(Connection dbConnection, String myNumber) throws SQLException {
        ResultSet rs = null;
        int totalCount = 0;
        try {
            // set Database Connection
            setDbConnection(dbConnection);
            // set Database Type
            setDatabaseType();
            // get sql statement
            String sql = TempLogQuerySqlStore.getMaxSequenceOfMyNumberSelectSql(getDatabaseType());
            // set PrepareStatement
            setPreparedStatement(sql);
            // set parameters of Preparestatement
            this.setStringPreparedStatement(1, myNumber);

            // execute sql
            rs = getPreparedStatement().executeQuery();
            while (rs.next()) {
                totalCount = rs.getInt("maxSequence");
            }

        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex2) {
            }
        }
        return totalCount;
    }

    private TempLogQueryVoList getTempLogQueryVoListFromResultSet(ResultSet resultSet,String caller) throws SQLException {
        TempLogQueryVoList tempLogQueryVoList = new TempLogQueryVoList();
        while (resultSet.next()) {
            TempLogQueryVo tempLogQueryVo = new TempLogQueryVo();
            
            tempLogQueryVo.setMyNumber(resultSet.getString("mynumber"));
            tempLogQueryVo.setMyName(resultSet.getString("myname"));
            tempLogQueryVo.setYourNumber(resultSet.getString("yournumber"));
            tempLogQueryVo.setYourName(resultSet.getString("yourname"));
            tempLogQueryVo.setSequence(resultSet.getInt("seq"));
            tempLogQueryVo.setStatus(resultSet.getString("status"));
            tempLogQueryVo.setCheckNumber(resultSet.getString("check_num"));
            tempLogQueryVo.setPacketData(resultSet.getString("packet_data"));
            tempLogQueryVo.setBusyMinute(resultSet.getInt("busy_min"));
            tempLogQueryVo.setWriteTime(resultSet.getTimestamp("write_time"));
            tempLogQueryVo.setLinkSequence(resultSet.getInt("link_seq"));
            if ( caller.equals("DetailView") ) {
                tempLogQueryVo.setRowNumber(resultSet.getInt("rownum"));
            }

            tempLogQueryVoList.add(tempLogQueryVo);
        }
        return tempLogQueryVoList;
    }

}
